﻿using System;
using System.Data;
using System.Data.SqlClient;
using StockholmsStad.Database.Procedures.Enum;

namespace StockholmsStad.Database.Procedures
{
    internal class ParvisStoredProcedure
    {
        /// <summary>
        /// Namn på db mail procedur
        /// </summary>
        private const string DB_MAIL_PROC = "msdb.dbo.sp_send_dbmail";

        /// <summary>
        /// Vilken databasprofil som ska användas
        /// </summary>
        private const string DB_MAIL_PROFILE = "ParVisMailProfile";

        /// <summary>
        /// Selectsats för EmailTemplate
        /// </summary>
        private const string SELECT_EMAIL_TEMPLATE_SQL =
            @"SELECT Subject, Body FROM dbo.EmailTemplate WHERE EmailTemplateId = {0}";

        /// <summary>
        /// Fromateringsargument för body
        /// </summary>
        private readonly object[] _bodyArguments;

        /// <summary>
        /// Vilken EmailTemplate som ska användas
        /// </summary>
        private readonly EmailTemplate _emailTemplate;

        /// <summary>
        /// Emailadress för mottagare
        /// </summary>
        private readonly string _recipient;

        private string _body;
        private string _subject;

        /// <summary>
        /// Skapar en ny instans av storedprocedure
        /// </summary>
        /// <param name="emailTemplate"></param>
        /// <param name="recipient"></param>
        /// <param name="bodyArguments"></param>
        internal ParvisStoredProcedure(EmailTemplate emailTemplate, string recipient, object[] bodyArguments)
        {
            _emailTemplate = emailTemplate;
            _bodyArguments = bodyArguments;
            _recipient = recipient;
        }

        /// <summary>
        /// Exekverar stored procedure
        /// </summary>
        internal void ExecuteProcedure()
        {
            using (SqlConnection conn = new SqlConnection() {ConnectionString = "Context Connection=true",})
            {
                conn.Open();
                RetrieveEmailTemplate(conn);
                _body = string.Format(_body, _bodyArguments);
                SendEmail(conn);
            }
        }

        /// <summary>
        /// Själva skickandet av mailet
        /// </summary>
        /// <param name="conn"></param>
        private void SendEmail(SqlConnection conn)
        {
            SqlCommand sendMailCmd = new SqlCommand(DB_MAIL_PROC, conn)
                                         {
                                             CommandType = CommandType.StoredProcedure,
                                         };
            sendMailCmd.Parameters.AddWithValue("@recipients", _recipient);
            sendMailCmd.Parameters.AddWithValue("@body", _body);
            sendMailCmd.Parameters.AddWithValue("@subject", _subject);
            sendMailCmd.Parameters.AddWithValue("@profile_name", DB_MAIL_PROFILE);
            sendMailCmd.ExecuteNonQuery();
        }

        /// <summary>
        /// Returnerar body och subject från template
        /// </summary>
        /// <param name="conn"></param>
        private void RetrieveEmailTemplate(SqlConnection conn)
        {
            SqlCommand emailTemplateCmd = new SqlCommand()
                                              {
                                                  Connection = conn,
                                                  CommandText =
                                                      string.Format(
                                                      SELECT_EMAIL_TEMPLATE_SQL,
                                                      (int) _emailTemplate)
                                              };
            using (SqlDataReader rdr = emailTemplateCmd.ExecuteReader())
            {
                if (rdr != null && rdr.HasRows)
                {
                    // Hämta första raden
                    rdr.Read();
                    _subject = rdr.GetString(0);
                    _body = rdr.GetString(1);
                    _body = _body.Replace("\\n", "\n");
                }
                else
                {
                    throw new Exception("Hittade inte EmailTemplate");
                }
            }
        }
    }
}